﻿Imports System.Data

Public Class DataSetHelper
    Public ds As DataSet
    Private m_FieldInfo As System.Collections.ArrayList
    Private m_FieldList As String
    Private GroupByFieldInfo As System.Collections.ArrayList
    Private GroupByFieldList As String

    Public Sub New(ByRef dataSet As DataSet)
        ds = dataSet
    End Sub

    Public Sub New()
        ds = Nothing
    End Sub

    Class FieldInfo
        Public RelationName As String
        Public FieldName As String   'source table field name
        Public FieldAlias As String  'destination table field name
        Public Aggregate As String
    End Class

    Private Sub ParseFieldList(ByVal FieldList As String, ByVal AllowRelation As Boolean)
        ' This code parses FieldList into FieldInfo objects  and then 
        ' adds them to the m_FieldInfo private member
        ' 
        ' FieldList systax:  [relationname.]fieldname[ alias], ...
        If (m_FieldList = FieldList) Then Return

        m_FieldInfo = New System.Collections.ArrayList()
        m_FieldList = FieldList

        Dim Field As FieldInfo
        Dim FieldParts As String()
        Dim Fields As String() = FieldList.Split(",")
        Dim i As Integer

        For i = 0 To Fields.Length - 1
            Field = New FieldInfo()
            'parse FieldAlias
            FieldParts = Fields(i).Trim().Split(" ")

            Select Case FieldParts.Length
                Case 1
                    Exit Select
                Case 2
                    Field.FieldAlias = FieldParts(1)
                    Exit Select
                Case Else
                    Throw New Exception("Too many spaces in field definition: '" + Fields(i) + "'.")
            End Select

            'parse FieldName and RelationName
            FieldParts = FieldParts(0).Split(".")
            Select FieldParts.Length
                Case 1
                    Field.FieldName = FieldParts(0)
                    Exit Select
                Case 2
                    If (AllowRelation = False) Then Throw New Exception("Relation specifiers not permitted in field list: '" + Fields(i) + "'.")
                    Field.RelationName = FieldParts(0).Trim()
                    Field.FieldName = FieldParts(1).Trim()
                    Exit Select
                Case Else
                    Throw New Exception("Invalid field definition: " + Fields(i) + "'.")
            End Select
            If (Field.FieldAlias Is Nothing) Then Field.FieldAlias = Field.FieldName
            m_FieldInfo.Add(Field)
        Next
    End Sub

    Private Sub ParseGroupByFieldList(ByVal FieldList As String)
        ' Parses FieldList into FieldInfo objects and adds them to the GroupByFieldInfo private member
        ' FieldList syntax: fieldname[ alias]|operatorname(fieldname)[ alias],...
        ' Supported Operators: count,sum,max,min,first,last

        If (GroupByFieldList = FieldList) Then Return

        GroupByFieldInfo = New System.Collections.ArrayList()

        Dim Field As FieldInfo
        Dim FieldParts As String()
        Dim Fields As String() = FieldList.Split(",")

        For i As Integer = 0 To Fields.Length - 1
            Field = New FieldInfo()
            'Parse FieldAlias
            FieldParts = Fields(i).Trim().Split(" ")
            Select Case FieldParts.Length
                Case 1
                    Exit Select
                Case 2
                    Field.FieldAlias = FieldParts(1)
                    Exit Select
                Case Else
                    Throw New ArgumentException("Too many spaces in field definition: '" + Fields(i) + "'.")
            End Select

            'Parse FieldName and Aggregate
            FieldParts = FieldParts(0).Split("(")
            Select Case FieldParts.Length
                Case 1
                    Field.FieldName = FieldParts(0)
                    Exit Select
                Case 2
                    Field.Aggregate = FieldParts(0).Trim().ToLower() 'we're doing a case-sensitive comparison later
                    Field.FieldName = FieldParts(1).Trim(" ", ")")
                    Exit Select
                Case Else
                    Throw New ArgumentException("Invalid field definition: '" + Fields(i) + "'.")
            End Select

            If (Field.FieldAlias Is Nothing) Then
                If (Field.Aggregate Is Nothing) Then
                    Field.FieldAlias = Field.FieldName
                Else
                    Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName
                End If
            End If
            GroupByFieldInfo.Add(Field)
        Next
        GroupByFieldList = FieldList
    End Sub

    Public Function CreateGroupByTable(ByVal TableName As String, ByVal SourceTable As DataTable, ByVal FieldList As String) As DataTable
        ' Creates a table based on aggregates of fields of another table
        ' RowFilter affects rows before GroupBy operation. No "Having" support
        ' though this can be emulated by subsequent filtering of the table that results
        ' FieldList syntax: fieldname[ alias]|aggregatefunction(fieldname)[ alias], ...

        If (FieldList Is Nothing) Then
            Throw New ArgumentException("You must specify at least one field in the field list.")
            'return CreateTable(TableName, SourceTable)
        Else
            Dim dt As DataTable = New DataTable(TableName)
            ParseGroupByFieldList(FieldList)
            For Each Field As FieldInfo In GroupByFieldInfo
                Dim dc As DataColumn = SourceTable.Columns(Field.FieldName)
                If (Field.Aggregate Is Nothing) Then
                    dt.Columns.Add(Field.FieldAlias, dc.DataType, dc.Expression)
                Else
                    dt.Columns.Add(Field.FieldAlias, dc.DataType)
                End If
            Next

            If (Not ds Is Nothing) Then ds.Tables.Add(dt)

            Return dt
        End If
    End Function

    Public Sub InsertGroupByInto(ByVal DestTable As DataTable, ByVal SourceTable As DataTable, ByVal FieldList As String, ByVal RowFilter As String, ByVal GroupBy As String)
        '
        ' Copies the selected rows and columns from SourceTable and inserts them into DestTable
        ' FieldList has same format as CreateGroupByTable
        ' 
        If (FieldList Is Nothing) Then Throw New ArgumentException("You must specify at least one field in the field list.")

        ParseGroupByFieldList(FieldList) ' parse field list
        ParseFieldList(GroupBy, False)        ' parse field names to Group By into an arraylist
        Dim Rows As DataRow() = SourceTable.Select(RowFilter, GroupBy)
        Dim LastSourceRow As DataRow = Nothing
        Dim DestRow As DataRow = Nothing
        Dim SameRow As Boolean
        Dim RowCount As Integer = 0
        For Each SourceRow As DataRow In Rows
            SameRow = False
            If (Not LastSourceRow Is Nothing) Then
                SameRow = True
                For Each Field As FieldInfo In m_FieldInfo
                    If (Not ColumnEqual(LastSourceRow(Field.FieldName), SourceRow(Field.FieldName))) Then
                        SameRow = False
                        Exit For
                    End If
                Next
                If (Not SameRow) Then DestTable.Rows.Add(DestRow)
            End If

            If (Not SameRow) Then
                DestRow = DestTable.NewRow()
                RowCount = 0
            End If
            RowCount += 1
            For Each Field As FieldInfo In GroupByFieldInfo
                Select Case Field.Aggregate    'this test is case-sensitive
                    Case Nothing, "", "last"
                        DestRow(Field.FieldAlias) = SourceRow(Field.FieldName)
                        Exit Select
                    Case "first"
                        If (RowCount = 1) Then DestRow(Field.FieldAlias) = SourceRow(Field.FieldName)
                        Exit Select
                    Case "count"
                        DestRow(Field.FieldAlias) = RowCount
                        Exit Select
                    Case "sum"
                        DestRow(Field.FieldAlias) = Add(DestRow(Field.FieldAlias), SourceRow(Field.FieldName))
                        Exit Select
                    Case "max"
                        DestRow(Field.FieldAlias) = Max(DestRow(Field.FieldAlias), SourceRow(Field.FieldName))
                        Exit Select
                    Case "min"
                        If (RowCount = 1) Then
                            DestRow(Field.FieldAlias) = SourceRow(Field.FieldName)
                        Else
                            DestRow(Field.FieldAlias) = Min(DestRow(Field.FieldAlias), SourceRow(Field.FieldName))
                        End If
                        Exit Select
                End Select
            Next
            LastSourceRow = SourceRow
        Next
        If (Not DestRow Is Nothing) Then DestTable.Rows.Add(DestRow)
    End Sub

    Private Function LocateFieldInfoByName(ByVal FieldList As System.Collections.ArrayList, ByVal Name As String) As FieldInfo
        ' Looks up a FieldInfo record based on FieldName
        For Each Field As FieldInfo In FieldList
            If (Field.FieldName = Name) Then
                Return Field
            End If
        Next

        Return Nothing
    End Function

    Private Function ColumnEqual(ByVal a As Object, ByVal b As Object) As Boolean
        ' Compares two values to see if they are equal. Also compares DBNULL.Value.
        ' Note: If your DataTable contains object fields, you must extend this
        ' function to handle them in a meaningful way if you intend to group on them.

        If IsDBNull(a) And IsDBNull(b) Then
            Return True    'both are null
        End If

        If IsDBNull(a) Or IsDBNull(b) Then
            Return False  'only one is null
        End If

        Return (a = b)    'value type standard comparison
    End Function

    Private Function Min(ByVal a As Object, ByVal b As Object) As Object
        'Returns MIN of two values - DBNull is less than all others
        If isDBNull(a) Or isdBNull(b) Then
            Return DBNull.Value
        End If

        If DirectCast(a, IComparable).CompareTo(b) = -1 Then
            Return a
        Else
            Return b
        End If
    End Function

    Private Function Max(ByVal a As Object, ByVal b As Object) As Object
        'Returns Max of two values - DBNull is less than all others
        If isDBNull(a) Then
            Return b
        End If

        If isDBNull(b) Then
            Return a
        End If
        If DirectCast(a, IComparable).CompareTo(b) = -1 Then
            Return b
        Else
            Return a
        End If
    End Function

    Private Function Add(ByVal a As Object, ByVal b As Object) As Object
        ' Adds two values - if one is DBNull, then returns the other
        If isDBNull(a) Then
            Return b
        End If

        If IsDBNull(b) Then
            Return a
        End If

        Return (CDec(a) + CDec(b))
    End Function

    Public Function SelectGroupByInto(ByVal TableName As String, ByVal SourceTable As DataTable, ByVal FieldList As String, ByVal RowFilter As String, ByVal GroupBy As String) As DataTable
        ' Selects data from one DataTable to another and performs various aggregate functions
        ' along the way. See InsertGroupByInto and ParseGroupByFieldList for supported aggregate functions.

        Dim dt As DataTable = CreateGroupByTable(TableName, SourceTable, FieldList)
        InsertGroupByInto(dt, SourceTable, FieldList, RowFilter, GroupBy)

        Return dt
    End Function
End Class

